import pymysql
from todo import Todo
class Mysql():
    @staticmethod
    def get():
        return  pymysql.Connection(host='192.168.138.130',database='test01',user='root',password='123456')
    def show(self):
        with self.get()as a:
            currsor=a.cursor()
            result=currsor.execute('select * from student')
            data=currsor.fetchall()
            return data
    def init_db(self):
        with self.get()as b:
            currsor=b.cursor()
            create='''CREATE TABLE IF NOT EXISTS todos(
            todo_id INT(10) PRIMARY key unique auto_increment not null,
            title VARCHAR(20) DEFAULT '',
            completed int DEFAULT 0
            );'''
            result=currsor.execute(create)
            data=currsor.fetchall()
            print(data)

    def create(self,tittle:str):
        with self.get()as c:
            currsor=c.cursor()
            result=currsor.execute('insert into todos(tittle) value(%s)',(tittle,))
            currsor.execute('select Last_insert_id()')
            (todo_id,)=currsor.fetchone()
            return Todo(tittle,todo_id)
            #print(Todo(tittle,todo_id))
    def convert_row_to_object(self,i):
        return Todo(i[1],id=i[0],completed=True if i[2]==1 else False)
    def list_todos(self):
        with self.get() as d:
            currsor=d.cursor()
            currsor.execute('select * from todos')
            data=currsor.fetchall()
            list = []
            for i in data:
                list.append(self.convert_row_to_object(i))
            return list

    def get_by_id(self,todo_id):
        with self.get()as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todos where todo_id like '%'")
            data = cursor.fetchall()
        list = []
        for i in range(len(data)):
            if i == todo_id:
                return Todo(data[i-1][1], id=data[i-1][0], completed=True if data[i-1][2] == 1 else False)
                #return data[i]
    def mark_completed(self,change_id):
        with self.get()as conn:
            currsor=conn.cursor()
            currsor.execute('update todos set completed =1 where todo_id=%s',(change_id))
            conn.commit()
            return self.get_by_id(change_id)
    '''def mark_completed(self,change_id):
         return self.get_by_id(change_id)'''
if __name__ == '__main__':
    exer=Mysql()
    exer.show()
    exer.init_db()
    exer.create('poi')
    print([x.__dict__ for x in exer.list_todos()])
    print(exer.get_by_id(3).__dict__)
    #print(exer.get_by_id(3))
    print(exer.mark_completed(3).__dict__)



